Release 10.1A: OpenEdge Data Management:
SQL Reference

Bit string data types

Bit string data types are used to define bit strings, which are sequences of bits having the value of either 0 or 1. This is the syntax for a bit string data type:

Syntax

BIT | BINARY | VARBINARY | LVARBINARY [ ( length ) ]

BIT

Corresponds to a single bit value of 0 or 1.

SQL statements can assign and compare values in BIT columns to and from columns of types CHAR, VARCHAR, BINARY, VARBINARY, TINYINT, SMALLINT, and INTEGER. However, in assignments from BINARY and VARBINARY, the value of the first four bits must be 0001 or 0000.

No arithmetic operations are allowed on BIT columns.

BINARY [ ( length ) ]

Corresponds to a bit field of the specified length of bytes. The default length is 1 byte. The maximum length is 2000 bytes.

When inserting literals into binary data types, INSERT statements must use a special format to store values in BINARY columns. They can specify the binary values as a bit string, hexadecimal string, or character string. INSERT statements must enclose binary values in single-quote marks, preceded by b for a bit string and x for a hexadecimal string. Table 48 lists the specification formats for binary values.

Table 48: Specification formats for binary values 

Specification	Format	Example
Bit string	b ' '	b '1010110100010000'
Hexadecimal string	x ' '	x 'ad10'
Character string	' '	'ad10'

SQL interprets a character string as the character representation of a hexadecimal string.

If the data inserted into a BINARY column is less than the length specified, SQL pads it with zeros.

BINARY data can be assigned and compared to and from columns of type BIT, CHAR, and VARBINARY. Arithmetic operations are not allowed.

VARBINARY ( length )

Corresponds to a variable-length bit field of the specified length in bytes. The default length is 1 byte. The maximum length is 31,995 bytes. The default length is 1.

Note: Due to index limitations, only the narrower VARBINARY columns can be indexed.

LVARBINARY ( length )

Corresponds to an arbitrarily long byte array with the maximum length defined by the amount of available disk storage up to 1,073,741,823. A BLOB is an object of data type LVARBINARY.

Maximum length for VARBINARY

The maximum length of the VARBINARY data type depends on:

The number of columns in a table — More columns in a table further limits the length of VARBINARY data.
When a table was created — Tables created earlier can support longer VARBINARY data than tables created later.

LVARBINARY limitations

Current limitations for LVARBINARY support are listed below:

LVARBINARY data type will only be accessible from the SQL Engine. LVARBINARY data columns added to tables created by the Progress 4GL are not visible to the 4GL.
LVARBINARY data columns cannot be part of an index.
LVARBINARY data columns cannot be used for variables or as parameters in stored procedures.
Comparison operations are not supported on LVARBINARY columns. Comparison operations between LVARBINARY columns are not supported. Comparison operations between LVARBINARY columns and columns of other data types are not supported.
Conversion, aggregate, and scalar functions are disallowed on this data type.
LVARBINARY does not have National Language Support (NLS).

Language support for LVARBINARY

This data type has normal column functionality except for the following exceptions:

A column of data type LVARBINARY is not a valid column name in a CREATE INDEX statement.
When issuing a CREATE TABLE statement, a valid data type for the column definitions is LVARBINARY. However, LVARBINARY does not allow the column constraints of PRIMARY KEY, FOREIGN KEY, UNIQUE, REFERENCES, and CHECK.
Note: When creating a table with a column of data type LVARBINARY, place the table in a new AREA.
The VALUES option on the INSERT statement is not valid for the LVARBINARY data type.
In a SELECT statement, a WHERE, GROUP BY, HAVING, or ORDER BY clause cannot use a column of data type LVARBINARY.
There is no support for an UPDATE of an LVARBINARY column on a table that contains a column of data type LVARBINARY. Obtain the functionality of an UPDATE on an LVARBINARY column by using the DELETE and INSERT statements for the record.

Utility support for LVARBINARY

Use BINARY DUMP/LOAD to dump and load data that contains the LVARBINARY data type.

Note: SQLDUMP and SQLLOAD do not support tables with LVARBINARY column data.